package cn.dida.dao;

import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import cn.dida.model.Pageable;
import cn.dida.model.SalesNewMan;
import cn.dida.model.Salesman;
import cn.dida.model.SalesmanSaleHistory;
import cn.dida.util.DateUtils;

/**
 * 促销员管理 数据交互类
 * @author weiwei
 */
@Repository
public class SalesmanScanDao extends DaoBase {

	/**
	 * 查询促销记录列表
	 * @author kelan
	 * @date 2017年9月5日
	 * @param companyId
	 * @param proName
	 * @param saleman
	 * @param timeLimitStart
	 * @param timeLimitEnd
	 * @param page
	 * @return
	 */
	public Pageable<Map<String, Object>> queryList(Integer companyId,String qrcode, String proName, String saleman, Long timeLimitStart, Long timeLimitEnd, Integer page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("");
        StringBuffer sqlQueryResult = new StringBuffer("" );
        sqlQueryResult.append(" select c.id,d.`name` proname,d.number procode,b.`name` salesname,   " ) ; 
        sqlQueryResult.append(" b.phone,case when IFNULL(a.sale_state,0)=0 then '产品未销售'     " ) ; 
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =0 then '身份未审核'  " ) ;  
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =1 and a.state =1 then '本人已消费' " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =1 then '正常'    " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =2 and a.state =1 then '本人已消费' " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =2 then '正常'     " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =3 then '审核不通过'   " ) ;  
        sqlQueryResult.append(" end state,a.c_time,case when ba.state=3 then '规则已终止'  " ) ;
        sqlQueryResult.append(" when ba.state=1 and !(a.c_time>=ba.valid_begin_time and a.c_time <= ba.valid_end_time) then '规则已失效' " ) ;
        sqlQueryResult.append(" when ba.state=1 and (a.c_time>=ba.valid_begin_time and a.c_time <= ba.valid_end_time) then '规则正常' " ) ;
        sqlQueryResult.append(" else '规则未启用' end payrulesate, case when a.cash_state = 0 then '未提现' when a.cash_state = 1 then '已提现' " ) ;
        sqlQueryResult.append(" else '提现失败' end txstate " ) ;
        sqlQueryResult.append(" from salesman_scan a    " ) ;
        sqlQueryResult.append(" join salesman b on a.salesman_id = b.id    " ) ;
        sqlQueryResult.append(" join qrcode_detail_1 c on a.qrcode_uuid = c.uuid    " ) ;
        sqlQueryResult.append(" join product d on a.product_id = d.id " ) ;
        sqlQueryResult.append(" left join salesman_payrule_base ba on a.payrule_base_id = ba.id " ) ;
      
        StringBuffer sqlWhere = new StringBuffer(" where a.company_id = ?  ");
        args = ArrayUtils.add(args, companyId);
        if (qrcode != null && qrcode !="") {
            sqlWhere.append(" and c.id = ?");
            args = ArrayUtils.add(args,  qrcode);
        }
        if (proName != null && proName !="") {
            sqlWhere.append(" and (d.`name` like ? or d.number like ? ) ");
            args = ArrayUtils.add(args, "%" + proName + "%");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (b.name like ? or b.phone like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if (timeLimitStart != null) {
            sqlWhere.append(" and a.c_time >= ?");
            args = org.apache.commons.lang3.ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null) {
            sqlWhere.append(" and a.c_time <= ?");
            
            args = org.apache.commons.lang3.ArrayUtils.add(args, timeLimitEnd);
        }
        sqlQueryResultCount.append(" select count(1) from ( " + sqlQueryResult.append(sqlWhere).toString() + " )b "   );
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append("  order by a.c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 促销记录导出
     * @author kelan
     * @date 2017年9月5日
     * @param companyId
     * @param proName
     * @param saleman
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String, Object>> exportList(Integer companyId,String qrcode, String proName, String saleman, Long timeLimitStart, Long timeLimitEnd){
    	Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("" );
        sqlQueryResult.append(" select c.id,d.`name` proname,d.number procode,b.`name` salesname,   " ) ; 
        sqlQueryResult.append(" b.phone,case when IFNULL(a.sale_state,0)=0 then '产品未销售'     " ) ; 
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =0 then '身份未审核'  " ) ;  
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =1 and a.state =1 then '本人已消费' " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =1 then '正常'    " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =2 and a.state =1 then '本人已消费' " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =2 then '正常'     " ) ;
        sqlQueryResult.append(" when IFNULL(a.sale_state,0)=1 and b.state =3 then '审核不通过'   " ) ;  
        sqlQueryResult.append(" end state,a.c_time,case when ba.state=3 then '规则已终止'  " ) ;
        sqlQueryResult.append(" when ba.state=1 and !(a.c_time>=ba.valid_begin_time and a.c_time <= ba.valid_end_time) then '规则已失效' " ) ;
        sqlQueryResult.append(" when ba.state=1 and (a.c_time>=ba.valid_begin_time and a.c_time <= ba.valid_end_time) then '规则正常' " ) ;
        sqlQueryResult.append(" else '规则未启用' end payrulesate, case when a.cash_state = 0 then '未提现' when a.cash_state = 1 then '已提现' " ) ;
        sqlQueryResult.append(" else '提现失败' end txstate " ) ;
        sqlQueryResult.append(" from salesman_scan a    " ) ;
        sqlQueryResult.append(" join salesman b on a.salesman_id = b.id    " ) ;
        sqlQueryResult.append(" join qrcode_detail_1 c on a.qrcode_uuid = c.uuid    " ) ;
        sqlQueryResult.append(" join product d on a.product_id = d.id " ) ;
        sqlQueryResult.append(" left join salesman_payrule_base ba on a.payrule_base_id = ba.id " ) ;
        
        StringBuffer sqlWhere = new StringBuffer(" where a.company_id =?  ");
        args = ArrayUtils.add(args, companyId);
        if (qrcode != null && qrcode !="") {
            sqlWhere.append(" and c.id = ?");
            args = ArrayUtils.add(args,  qrcode);
        }
        if (proName != null && proName !="") {
            sqlWhere.append(" and (d.`name` like ? or d.number like ? ) ");
            args = ArrayUtils.add(args, "%" + proName + "%");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (b.name like ? or b.phone like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if (timeLimitStart != null ) {
            sqlWhere.append(" and a.c_time >= ?");
            args = org.apache.commons.lang3.ArrayUtils.add(args, timeLimitStart);
        }
        if (timeLimitEnd != null ) {
            sqlWhere.append(" and a.c_time <= ?");
            args = org.apache.commons.lang3.ArrayUtils.add(args, timeLimitEnd);
        }
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by a.c_time desc ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }
    
}